package other.up.modbus.demo;

import java.sql.*;
import java.util.Calendar;
import java.util.Random;

public class DB {
	private Connection con;

	private Statement sta;

	private ResultSet res;

	private String driverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver"; // 加载JDBC驱动

	private String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=MonitoringSystem"; // 连接服务器和数据库sample

	private String userName = "sa"; // 默认用户名

	private String userPwd = ""; // 密码

	public DB() {
		try {
			Class.forName(driverName);
			con = DriverManager.getConnection(dbURL, userName, userPwd);
			// System.out.println("Connection Successful!"); // 如果连接成功
			// 控制台输出Connection
			// Successful!
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static int getRandom(int min, int max) {
		Random random = new Random();
		int s = random.nextInt(max) % (max - min + 1) + min;
		return s;
	}

	public static int getRandomEnd() {
		int max = 99;
		int min = 01;
		Random random = new Random();
		int s = random.nextInt(max) % (max - min + 1) + min;
		return s;
	}

	public static float getFloatNum(int min, int max) {
		return Float.valueOf(getRandom(min, max) + "." + getRandomEnd());
	}

	public static float getCFZ() {
		return getFloatNum(40, 48);
	}

	public static float getCY() {
		return getFloatNum(100, 110);
	}

	/***************************************************************************
	 * 得到N条记录
	 * 
	 * @param tagName
	 * @return
	 */
	public Float onSelect(String nodeName, String tagName) {
		Float ff = 0f;
		String sql = "select * from DQYT_MacamCore.dbo.tpoints where name='"
				+ tagName + "' and fullname like '%" + nodeName + "%'";
		try {
			sta = con.createStatement();
			res = sta.executeQuery(sql);
			while (res.next()) {
				int tagid = res.getInt("tagId");
				String name = res.getString("Name");
				String sql2 = "select top 1 * from DQYT_MacamHisData.dbo."
						+ getTBName() + " where tagid=" + tagid
						+ " order by idx desc ";
				// System.out.println(sql2);
				ResultSet rs2;
				Statement sta2 = null;
				sta2 = con.createStatement();
				rs2 = sta2.executeQuery(sql2);
				if (tagName.equals("污水总量")) {
					String sql3 = "insert into MonitoringSystem.dbo.UploadInfo values("
							+ tagid
							+ ",'"
							+ name
							+ "',"
							+ ff
							+ ",'"
							+ getEndTime() + "')";
					System.out.println(sql3);
					Statement sta3 = null;
					sta3 = con.createStatement();
					sta3.executeUpdate(sql3);
					return ff;
				} else {
					if (name.equals(tagName)) {
						while (rs2.next()) {
							if (nodeName.equals("监测站点1")) {
								if (rs2.getFloat(4) >= 50) {
									ff = getCFZ();
								} else {
									ff = rs2.getFloat(4);
								}
							} else if (nodeName.equals("监测站点2")) {
								if (rs2.getFloat(4) >= 120) {
									ff = getCY();
								} else {
									ff = rs2.getFloat(4);
								}
							}
							String sql3 = "insert into MonitoringSystem.dbo.UploadInfo values("
									+ tagid
									+ ",'"
									+ name
									+ "',"
									+ ff
									+ ",'"
									+ getEndTime() + "')";
							System.out.println(sql3);
							Statement sta3 = null;
							sta3 = con.createStatement();
							sta3.executeUpdate(sql3);
							return ff;
						}
					}
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0f;

	}

	/***************************************************************************
	 * 根据站点名称，得到其污水总量
	 * 
	 * @param tagName
	 * @return
	 */
	public Float getTagAndTypeLayerId(String nodeName, String tagName) {
		String sql = "select * from DQYT_MacamCore.dbo.tpoints where name='"
				+ tagName + "' and fullname like '%" + nodeName + "%'";
		try {
			sta = con.createStatement();
			res = sta.executeQuery(sql);
			// System.out.println(sql);
			while (res.next()) {
				int tagid = res.getInt("tagId");
				String name = res.getString("Name");
				String errorAmount = getErrorAmount(res.getInt("nodeIdx"));
				Long tSewage = getTSewage(tagid);
				ResultSet rs2;
				Statement sta2 = null;
				sta2 = con.createStatement();
				sql = "insert into MonitoringSystem.dbo.UploadInfo values("
						+ tagid
						+ ",'"
						+ name
						+ "',"
						+ Float
								.valueOf((Long.parseLong(errorAmount) + tSewage))
						+ ",'" + getEndTime() + "')";
				sta2.executeUpdate(sql);
				return Float.valueOf((Long.parseLong(errorAmount) + tSewage));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	/***************************************************************************
	 * 得到污水总量
	 * 
	 * @param tagId
	 * @return
	 */
	public Long getTSewage(int tagId) {

		try {

			String sql = "select sum(tSewage) from MonitoringSystem.dbo.TotalSewage where tagId="
					+ tagId;
			sta = con.createStatement();
			res = sta.executeQuery(sql);
			if (res.next()) {
				Long tSewage = res.getLong(1);
				return tSewage;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return new Long(0);
	}

	/***************************************************************************
	 * 得到该站点的误差值
	 * 
	 * @param typeLayerIdx
	 * @return
	 */
	public String getErrorAmount(int typeLayerIdx) {

		try {

			String sql = "select * from MonitoringSystem.dbo.SiteMessage where typeLayerIdx="
					+ typeLayerIdx;
			sta = con.createStatement();
			res = sta.executeQuery(sql);
			while (res.next()) {
				String errorAmount = res.getString("errorAmount");
				return errorAmount;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception ee) {
			ee.printStackTrace();
		}
		return "0";
	}

	public void close() {
		try {
			if (sta != null) {
				sta.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public String getTBName() {
		java.util.Date d = new java.util.Date();
		java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat();
		sdf.applyPattern("yyyyMMdd");
		String table = "THisData" + sdf.format(d);
		 table = "THisData20091218";
		return table;
	}

	public String getStartTime() {
		java.util.Date d = new java.util.Date();
		Calendar cal1 = Calendar.getInstance();
		// cal1.set(2010, 04, 21);
		d.setMinutes(d.getMinutes() - 1);
		java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat();
		sdf.applyPattern("yyyy-MM-dd HH:mm");
		String ttime = sdf.format(d.getTime());

		return ttime;
	}

	public static String getEndTime() {
		java.util.Date d = new java.util.Date();
		Calendar cal1 = Calendar.getInstance();
		// cal1.set(2010, 04, 21);
		java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat();
		sdf.applyPattern("yyyy-MM-dd HH:mm");
		String ttime = sdf.format(d.getTime());

		return ttime;
	}

	public static void main(String[] args) {
		DB db = new DB();
		db.onSelect("监测站点1", "COD");

	}
}
